﻿using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using TianYu.Core.Common.NPOI;
using System.Data;

namespace TianYu.Core.Common
{
    public class NPOIHelper
    {
        /// <summary>
        /// 将excel文件内容读取到DataTable数据表中
        /// </summary>
        /// <param name="fileName">文件完整路径名</param>
        /// <param name="sheetName">指定读取excel工作薄sheet的名称</param>
        /// <param name="isFirstRowColumn">第一行是否是DataTable的列名：true=是，false=否</param>
        /// <returns>DataTable数据表</returns>
        public static DataTable ReadExcelToDataTable(string fileName, Func<byte[], string, string> uploadImageFunc = null, string sheetName = null, bool isFirstRowColumn = true)
        {
            try
            {
                if (!File.Exists(fileName)) return null;

                //根据指定路径读取文件
                FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);

                return ReadStreamToDataTable(fs, uploadImageFunc, sheetName, isFirstRowColumn);
            }
            catch (TYBaseException) { throw; }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// 将文件流读取到DataTable数据表中
        /// </summary>
        /// <param name="fileStream">文件流</param>
        /// <param name="sheetName">指定读取excel工作薄sheet的名称</param>
        /// <param name="isFirstRowColumn">第一行是否是DataTable的列名：true=是，false=否</param>
        /// <returns>DataTable数据表</returns>
        public static DataTable ReadStreamToDataTable(Stream fileStream, Func<byte[], string, string> uploadImageFunc = null, string sheetName = null, bool isFirstRowColumn = true)
        {
            try
            {
                //定义要返回的datatable对象
                DataTable data = new DataTable();

                //根据文件流创建excel数据结构,NPOI的工厂类WorkbookFactory会自动识别excel版本，创建出不同的excel数据结构
                IWorkbook workbook = WorkbookFactory.Create(fileStream);

                //excel工作表
                ISheet sheet = null;

                //如果有指定工作表名称
                if (!string.IsNullOrEmpty(sheetName))
                {
                    sheet = workbook.GetSheet(sheetName);
                }
                if (sheet == null) sheet = workbook.GetSheetAt(0);

                //数据开始行(排除标题行)
                int startRow = 0;

                if (sheet != null)
                {
                    IRow firstRow = sheet.GetRow(0);
                    //一行最后一个cell的编号 即总的列数
                    int cellCount = firstRow.LastCellNum;
                    //如果第一行是标题列名
                    if (isFirstRowColumn)
                    {
                        for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                        {
                            ICell cell = firstRow.GetCell(i);
                            if (cell != null)
                            {
                                string cellValue = cell.ToString();
                                if (cellValue != null)
                                {
                                    DataColumn column = new DataColumn(cellValue);
                                    data.Columns.Add(column);
                                }
                            }
                        }
                        startRow = sheet.FirstRowNum + 1;
                    }
                    else
                    {
                        startRow = sheet.FirstRowNum;
                    }
                    //最后一列的标号
                    int rowCount = sheet.LastRowNum;

                    //获取图片
                    var isExistsImage = false;
                    var pictureList = new List<PicturesInfo>();
                    if (uploadImageFunc != null)
                    {
                        pictureList = sheet.GetAllPictureInfos();

                        isExistsImage = (pictureList != null && pictureList.Any());
                    }

                    for (int i = startRow; i <= rowCount; ++i)
                    {
                        IRow row = sheet.GetRow(i);
                        if (row == null || row.FirstCellNum < 0 || row.Cells.All(c => c.CellType == CellType.Blank)) continue; //没有数据的行默认是null　　　　　　　

                        DataRow dataRow = data.NewRow();
                        for (int j = row.FirstCellNum; j < cellCount; ++j)
                        {
                            //同理，没有数据的单元格都默认是null
                            ICell cell = row.GetCell(j);
                            if (cell != null)
                            {
                                if (cell.CellType == CellType.Numeric)
                                {
                                    //判断是否日期类型
                                    if (DateUtil.IsCellDateFormatted(cell))
                                    {
                                        dataRow[j] = row.GetCell(j).DateCellValue;
                                    }
                                    else
                                    {
                                        dataRow[j] = row.GetCell(j).ToString().Trim();
                                    }
                                }
                                else
                                {
                                    dataRow[j] = row.GetCell(j).ToString().Trim();

                                    if (isExistsImage && string.IsNullOrWhiteSpace(dataRow[j].ToString()))
                                    {
                                        var picture = pictureList.FirstOrDefault(x => x.MinCol == j && x.MinRow == i);

                                        if (picture != null)
                                        {
                                            dataRow[j] = uploadImageFunc(picture.PictureData, picture.PictureType.GetExtendName());
                                        }
                                    }
                                }
                            }
                        }
                        data.Rows.Add(dataRow);
                    }
                }
                return data;
            }
            catch (TYBaseException) { throw; }
            catch (Exception ex)
            {
                throw new TYBaseException("读取Excel异常：" + ex.Message);
            }
        }

        /// <summary>获取execl单元格</summary>
        public static async Task<Dictionary<int, List<ICell>>> GetExcelCells(Stream fileStream, Func<byte[], string, Task<string>> uploadImageFunc = null)
        {
            try
            {
                //根据文件流创建excel数据结构,NPOI的工厂类WorkbookFactory会自动识别excel版本，创建出不同的excel数据结构
                var workbook = WorkbookFactory.Create(fileStream);
                return await GetExcelCells(workbook, uploadImageFunc);
            }
            catch (TYBaseException) { throw; }
            catch (Exception ex)
            {
                throw new TYBaseException("读取Excel异常：" + ex.Message);
            }
        }

        /// <summary>获取execl单元格</summary>
        public static async Task<Dictionary<int, List<ICell>>> GetExcelCells(byte[] byteArray, Func<byte[], string, Task<string>> uploadImageFunc = null)
        {
            try
            {
                using (var ms = new MemoryStream(byteArray))
                {
                    var workbook = WorkbookFactory.Create(ms);
                    return await GetExcelCells(workbook, uploadImageFunc);
                }
            }
            catch (TYBaseException) { throw; }
            catch (Exception ex)
            {
                throw new TYBaseException("读取Excel异常：" + ex.Message);
            }
        }

        private static async Task<Dictionary<int, List<ICell>>> GetExcelCells(IWorkbook workbook, Func<byte[], string, Task<string>> uploadImageFunc = null)
        {
            Dictionary<int, List<ICell>> keyValues = new Dictionary<int, List<ICell>>();
            try
            {
                //根据文件流创建excel数据结构,NPOI的工厂类WorkbookFactory会自动识别excel版本，创建出不同的excel数据结构
                //excel工作表
                ISheet sheet = null;
                int sheetNumber = workbook.NumberOfSheets;

                for (int i = 0; i < sheetNumber; i++)
                {
                    List<ICell> listCell = new List<ICell>();
                    if (i > 0 || sheet == null)
                        sheet = workbook.GetSheetAt(i);

                    if (sheet == null || sheet.GetRow(0) == null) continue;

                    //获取图片
                    var isExistsImage = false;
                    var pictureList = new List<PicturesInfo>();
                    if (uploadImageFunc != null)
                    {
                        pictureList = sheet.GetAllPictureInfos();

                        isExistsImage = (pictureList != null && pictureList.Any());
                    }

                    //最后一行的标号
                    int rowCount = sheet.LastRowNum;
                    for (int r = 0; r <= rowCount; ++r)
                    {
                        IRow row = sheet.GetRow(r);

                        if (row == null || row.FirstCellNum < 0) continue; //没有数据的行默认是null　　　　　　　

                        //如果有图片先上传图片 
                        if (uploadImageFunc != null && isExistsImage)
                        {
                            for (int c = row.FirstCellNum; c < row.LastCellNum; ++c)
                            {
                                ICell cell = row.GetCell(c);
                                if (cell == null) continue;

                                if (i == 0 && r == 0 && c == 0) continue; //忽略第一个单元格的图片

                                if (pictureList.Exists(x => x.MinCol == c && x.MinRow == r))
                                {
                                    var picture = pictureList.FirstOrDefault(x => x.MinCol == c && x.MinRow == r);

                                    if (picture != null)
                                    {
                                        cell.SetCellValue(await uploadImageFunc(picture.PictureData, picture.PictureType.GetExtendName()));
                                    }
                                }
                            }
                        }

                        //去空格
                        row.Cells.ForEach(m => m.SetCellValue(m.ToString().Trim()));

                        listCell.AddRange(row.Cells);
                    }
                    keyValues.Add(i, listCell);
                }

                return keyValues;
            }
            catch (TYBaseException) { throw; }
            catch (Exception ex)
            {
                throw new TYBaseException("读取Excel异常：" + ex.Message);
            }
        }

        /// <summary>
        /// 动态生成Execl
        /// </summary>
        /// <param name="tableHeader">表头</param>
        /// <param name="dropDown"></param>
        /// <returns></returns>
        public static MemoryStream CreateExcel(List<TableParams> list)
        {
            if (list == null) return null;

            var model = new SheetModel { SheetName = "sheet1", TableParamsList = list };

            return CreateExcel(new List<SheetModel>() { model });
        }

        /// <summary>
        /// 动态生成Execl(单个sheet)
        /// </summary>
        /// <param name="model"></param> 
        public static MemoryStream CreateExcel(SheetModel model)
        {
            return CreateExcel(new List<SheetModel>() { model });
        }

        /// <summary>
        /// 动态生成Execl(多个sheet)
        /// </summary>
        /// <param name="list"></param> 
        public static MemoryStream CreateExcel(List<SheetModel> list)
        {
            if (list == null) return null;
            var workbook = new HSSFWorkbook();
            foreach (var item in list)
            {
                AddSheet(workbook, item);
            }
            MemoryStream ms = new MemoryStream();
            workbook.Write(ms);
            ms.Flush();
            ms.Position = 0;

            return ms;
        }

        /// <summary>
        /// 将数据写到现有Excel中
        /// </summary>
        /// <param name="fileStream">Excel文件流</param>
        /// <param name="cellInfoList">写入单元格数据</param> 
        public static MemoryStream WriteExcel(Stream fileStream, List<WriteCellDataModel> cellInfoList, bool leaveOpen)
        {
            try
            {
                //根据文件流创建excel数据结构,NPOI的工厂类WorkbookFactory会自动识别excel版本，创建出不同的excel数据结构
                IWorkbook workbook = WorkbookFactory.Create(fileStream);
                //excel工作表   
                var sheetNameList = cellInfoList.GroupBy(g => g.SheetName).Select(x => x.Key).ToList();

                foreach (var sheetName in sheetNameList)
                {
                    var sheet = workbook.GetSheet(sheetName);

                    var cellList = cellInfoList.Where(x => x.SheetName == sheetName).ToList();

                    foreach (var item in cellList)
                    {
                        var cell = sheet.GetRow(item.RowIndex).GetCell(item.ColumnIndex);
                        if (cell == null) cell = sheet.GetRow(item.RowIndex).CreateCell(item.ColumnIndex);
                        cell.SetCellValue(item.CellValue);
                    }
                }

                var ms = new MemoryStream();
                workbook.Write(ms, leaveOpen);
                ms.Flush();
                ms.Position = 0;

                return ms;
            }
            catch (Exception)
            {
                throw;
            }
        }



        /// <summary>
        /// 添加Sheet
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="model"></param>
        private static void AddSheet(HSSFWorkbook workbook, SheetModel model)
        {
            var sheet = workbook.CreateSheet(model.SheetName);
            //sheet.CreateFreezePane(list.Count, 1);

            IRow row = sheet.CreateRow(model.StartRowNum);
            int imageCellIndex = -1;
            int i = 0, j = 1;
            foreach (var item in model.TableParamsList)
            {
                sheet.SetColumnWidth(i, item.CellWidth); //单元格宽度 10个字符  

                ICell cell = row.CreateCell(i);
                cell.CellStyle = GetCellStyle(workbook, StyleEnum.Head);
                cell.SetCellValue(item.TableHeadName);

                //判断当前sheet是否有图片列
                if (!string.IsNullOrWhiteSpace(model.ImageCellName) && item.TableHeadName == model.ImageCellName) imageCellIndex = i;

                if (item.CellDropDownData != null)
                {
                    var dropDownData = item.CellDropDownData;
                    //设置列下拉数据
                    string CourseSheetName = item.TableHeadName, RangeName = "dicRange" + i;
                    ISheet CourseSheet = workbook.CreateSheet(CourseSheetName);
                    CourseSheet.CreateRow(0).CreateCell(0).SetCellValue(item.TableHeadName);

                    for (var k = 0; k < dropDownData.Length; k++)
                    {
                        CourseSheet.CreateRow(k + 1).CreateCell(0).SetCellValue(dropDownData[k]);
                    }

                    IName range = workbook.CreateName();
                    range.RefersToFormula = string.Format("{0}!$A$2:$A${1}", CourseSheetName, (dropDownData.Length + 1).ToString());
                    range.NameName = RangeName;

                    var regions = new CellRangeAddressList(1, 65535, i, i);
                    var constraint = DVConstraint.CreateFormulaListConstraint(RangeName);
                    var dataValidate = new HSSFDataValidation(regions, constraint);

                    //XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
                    //XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateExplicitListConstraint(item.CellDropDownData);
                    //var regions = new CellRangeAddressList(1, 65535, i, i);
                    //var dataValidate = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, regions);

                    sheet.AddValidationData(dataValidate);
                    //隐藏引用sheet
                    workbook.SetSheetHidden(j, true);
                    j++;
                }
                i++;
            }

            if (model.Data != null)
            {
                int rowIndex = model.StartRowNum + 1, cellIndex = 0;
                var cacheImage = new Dictionary<string, byte[]>();
                foreach (DataRow dr in model.Data.Rows)
                {
                    IRow newRow = sheet.CreateRow(rowIndex);
                    cellIndex = 0;
                    foreach (var item in dr.ItemArray)
                    {
                        ICell newCell = newRow.CreateCell(cellIndex);

                        if (imageCellIndex != -1 && imageCellIndex == cellIndex)
                        {
                            string imagePath = item?.ToString();

                            InsertImage(workbook, sheet, imagePath, rowIndex, cellIndex, cacheImage);
                        }
                        else
                        {
                            newCell.SetCellValue(item + "");
                        }

                        cellIndex++;
                    }
                    rowIndex++;
                }
            }

            if (model.RowData != null)
            {
                foreach (var item in model.RowData)
                {
                    IRow newRow = sheet.CreateRow(item.RowNum);
                    foreach (var cellData in item.CellData)
                    {
                        ICell newCell = newRow.CreateCell(cellData.Column);
                        newCell.SetCellValue(cellData.Content);

                        var range = new CellRangeAddress(cellData.firstRow, cellData.lastRow, cellData.firstCol, cellData.lastCol);

                        sheet.AddMergedRegion(range);
                    }
                }
            }
        }


        /// <summary>
        /// 定义单元格常用到样式
        /// </summary>
        /// <param name="wb"></param>
        /// <param name="str"></param>
        /// <returns></returns>
        private static ICellStyle GetCellStyle(IWorkbook wb, StyleEnum style)
        {
            ICellStyle cellStyle = wb.CreateCellStyle();

            //定义几种字体   
            IFont font12 = wb.CreateFont();
            font12.FontHeightInPoints = 10;
            font12.FontName = "微软雅黑";

            IFont font = wb.CreateFont();
            font.FontName = "微软雅黑";
            //font.Underline = 1;下划线   

            IFont fontcolorblue = wb.CreateFont();
            fontcolorblue.Color = HSSFColor.OliveGreen.Blue.Index;
            fontcolorblue.IsItalic = true;//下划线  
            fontcolorblue.FontName = "微软雅黑";


            //边框  
            //cellStyle.BorderBottom = BorderStyle.Dotted;
            //cellStyle.BorderLeft = BorderStyle.Hair;
            //cellStyle.BorderRight = BorderStyle.Hair;
            //cellStyle.BorderTop = BorderStyle.Dotted;
            ////边框颜色  
            //cellStyle.BottomBorderColor = HSSFColor.OliveGreen.Blue.Index;
            //cellStyle.TopBorderColor = HSSFColor.OliveGreen.Blue.Index;

            //背景图形
            //cellStyle.FillBackgroundColor = HSSFColor.OLIVE_GREEN.BLUE.index;  
            //cellStyle.FillForegroundColor = HSSFColor.OLIVE_GREEN.BLUE.index;  
            cellStyle.FillForegroundColor = HSSFColor.White.Index;
            // cellStyle.FillPattern = FillPatternType.NO_FILL;  
            cellStyle.FillBackgroundColor = HSSFColor.Blue.Index;

            //水平对齐  
            cellStyle.Alignment = HorizontalAlignment.Left;

            //垂直对齐  
            cellStyle.VerticalAlignment = VerticalAlignment.Center;

            //自动换行  
            cellStyle.WrapText = true;

            //缩进;当设置为1时，前面留的空白太大了  
            cellStyle.Indention = 0;

            switch (style)
            {
                case StyleEnum.Head:
                    cellStyle.Alignment = HorizontalAlignment.Center;
                    cellStyle.SetFont(font12);
                    break;
                case StyleEnum.Time:
                    IDataFormat datastyle = wb.CreateDataFormat();
                    cellStyle.DataFormat = datastyle.GetFormat("yyyy/mm/dd");
                    cellStyle.SetFont(font);
                    break;
                case StyleEnum.Number:
                    cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
                    cellStyle.SetFont(font);
                    break;
                case StyleEnum.Money:
                    IDataFormat format = wb.CreateDataFormat();
                    cellStyle.DataFormat = format.GetFormat("￥#,##0");
                    cellStyle.SetFont(font);
                    break;
                case StyleEnum.Url:
                    fontcolorblue.Underline = (FontUnderlineType)1;
                    cellStyle.SetFont(fontcolorblue);
                    break;
                case StyleEnum.Percentage:
                    cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");
                    cellStyle.SetFont(font);
                    break;
                case StyleEnum.ChineseCapital:
                    IDataFormat format1 = wb.CreateDataFormat();
                    cellStyle.DataFormat = format1.GetFormat("[DbNum2][$-804]0");
                    cellStyle.SetFont(font);
                    break;
                case StyleEnum.textLength:
                    cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00E+00");
                    cellStyle.SetFont(font);
                    break;
                case StyleEnum.Default:
                    cellStyle.SetFont(font);
                    break;
            }
            return cellStyle;


        }

        /// <summary>
        /// 插入某个图片到指定单元格
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="sheet"></param>
        /// <param name="imagePath"></param>
        /// <param name="row"></param>
        /// <param name="column"></param>
        private static void InsertImage(HSSFWorkbook workbook, ISheet sheet, string imagePath, int row, int column, Dictionary<string, byte[]> cacheImage)
        {
            if (imagePath.RegexIsMatch("(https?:[^:<>\"]*\\/)([^:<> \"]*)(\\.((png!thumbnail)|(png)|(jpg)|))"))
            {
                var pictureType = PictureType.JPEG;

                var len = imagePath.LastIndexOf('.');

                var imageExt = imagePath.Substring(len, imagePath.Length - len);

                if (string.Equals(imageExt, ".png", StringComparison.OrdinalIgnoreCase))
                {
                    pictureType = PictureType.PNG;
                }
                else if (string.Equals(imageExt, ".gif", StringComparison.OrdinalIgnoreCase))
                {
                    pictureType = PictureType.GIF;
                }

                var imagePathMD5 = Security.MD5Helper.MD5String(imagePath);

                if (!cacheImage.ContainsKey(imagePathMD5))
                {
                    cacheImage.Add(imagePathMD5, imagePath.GetImageByUrl());
                }

                if (cacheImage[imagePathMD5] != null)
                {
                    int pictureIdx = workbook.AddPicture(cacheImage[imagePathMD5], pictureType);

                    HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
                    // 插图片的位置  
                    HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, column, row, column + 1, row + 1);
                    // 把图片插到相应的位置
                    patriarch.CreatePicture(anchor, pictureIdx);
                }
            }
        }

    }
}
